package tw.com.softleader.reins.demo.exam.dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import tw.com.softleader.reins.demo.exam.entity.Exam;


@Repository
public class ExamDao {
	
	@Autowired
	private DataSource dataSource;

	public List<Exam> findAll() {
		List<Exam> exams=new ArrayList<Exam>();
		Connection conn = null;
		PreparedStatement pstmt =null; 
		ResultSet rs =null;
		
		try {     
			conn = dataSource.getConnection();
			String qryStmt = "SELECT * FROM exam " ;
			pstmt = conn.prepareStatement(qryStmt);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				Exam exam=new Exam();
				exam.setId(rs.getLong(1));
				exam.setName(rs.getString(2));
				exam.setDifficulty(rs.getLong(3));
				exam.setItemLength(rs.getLong(4));
				exam.setTime(rs.getLong(5));
				exams.add(exam);

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return exams;
		
	}// end of findAll()
	
	public Exam findById(Long id) {
		Exam exam=new Exam();
		Connection conn = null;
		PreparedStatement pstmt =null; 
		ResultSet rs =null;
		
		try {     
			conn = dataSource.getConnection();
			String qryStmt = "SELECT * FROM exam where id=?" ;
			pstmt = conn.prepareStatement(qryStmt);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			while(rs.next()) {
				exam.setId(rs.getLong(1));
				exam.setName(rs.getString(2));
				exam.setDifficulty(rs.getLong(3));
				exam.setItemLength(rs.getLong(4));
				exam.setTime(rs.getLong(5));
				

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if (rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (pstmt!=null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn!=null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return exam;
	}// end of findById()
	
	public String insert(Exam exam) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int i=1;
		try {
			conn = dataSource.getConnection();
			String qryStmt = "INSERT INTO exam VALUES (default,?,?,?,?)";
			pstmt = conn.prepareStatement(qryStmt);
			pstmt.setString(1, exam.getName());
			pstmt.setLong(2, exam.getDifficulty());
			pstmt.setLong(3, exam.getItemLength());
			pstmt.setLong(4, exam.getTime());
			i=pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return "共新增"+i+"筆資料";
	}
	
	public String update(Exam exam,Long id) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int i=0;
		try {
			conn = dataSource.getConnection();
			String qryStmt = "UPDATE exam SET name=?,difficult=?,itemLength=?,time=? where id=?";
			pstmt = conn.prepareStatement(qryStmt);
			pstmt.setString(1, exam.getName());
			pstmt.setLong(2, exam.getDifficulty());
			pstmt.setLong(3, exam.getItemLength());
			pstmt.setLong(4, exam.getTime());
			pstmt.setLong(5, id);
			i=pstmt.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return "共更新"+i+"筆資料";
	}
	
	public String delete() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		int i=0;
		try {
			conn = dataSource.getConnection();
			String qryStmt = "DELETE from exam";
			pstmt = conn.prepareStatement(qryStmt);
			i = pstmt.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			if (pstmt != null) {
				try {
					pstmt.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
		return "共刪除"+i+"筆資料";
	}
}
